package db

import (
	"context"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"net/url"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"sync"
	"time"

	"cloud.google.com/go/bigquery"
	"golang.org/x/net/proxy"
	"golang.org/x/oauth2"
	"golang.org/x/oauth2/google"
	"google.golang.org/api/iterator"
	"google.golang.org/api/option"
)

var (
	// formattingFiles 跟踪正在格式化的文件，避免循环触发
	formattingFiles = make(map[string]bool)
	formattingMutex sync.Mutex
)

type BigQueryDB struct {
	authClient    *bigquery.Client // Client for auth project (for queries)
	dataClient    *bigquery.Client // Client for data project (for metadata)
	authProjectID string           // Project for auth and billing
	dataProjectID string           // Project where data is located
	dataset       string
	ctx           context.Context
	readOnlyMode  bool
	logDir        string // 日志目录
	logPrefix     string // 日志文件名前缀
}

// IsFormattingFile 检查文件是否正在格式化
func IsFormattingFile(path string) bool {
	formattingMutex.Lock()
	defer formattingMutex.Unlock()
	return formattingFiles[path]
}

// SetFormattingFile 设置文件格式化状态
func SetFormattingFile(path string, formatting bool) {
	formattingMutex.Lock()
	defer formattingMutex.Unlock()
	if formatting {
		formattingFiles[path] = true
	} else {
		delete(formattingFiles, path)
	}
}

// testProxyConnection 测试代理连接
func testProxyConnection(proxyURL string) error {
	proxyURLParsed, err := url.Parse(proxyURL)
	if err != nil {
		return fmt.Errorf("invalid proxy URL: %w", err)
	}

	var transport *http.Transport
	if strings.HasPrefix(proxyURL, "socks5://") {
		dialer, err := proxy.FromURL(proxyURLParsed, proxy.Direct)
		if err != nil {
			return fmt.Errorf("failed to create SOCKS5 dialer: %w", err)
		}
		transport = &http.Transport{
			DialContext: dialer.(proxy.ContextDialer).DialContext,
		}
	} else {
		transport = &http.Transport{
			Proxy: http.ProxyURL(proxyURLParsed),
		}
	}

	client := &http.Client{
		Transport: transport,
		Timeout:   30 * time.Second,
	}

	// 测试连接到一个简单的HTTPS端点
	resp, err := client.Get("https://www.google.com/generate_204")
	if err != nil {
		return fmt.Errorf("proxy connection failed: %w", err)
	}
	defer resp.Body.Close()

	if resp.StatusCode != 204 {
		return fmt.Errorf("proxy returned unexpected status: %d", resp.StatusCode)
	}

	log.Printf("代理连接测试成功: %s", proxyURL)
	return nil
}

func NewBigQuery(authProjectID, dataProjectID, dataset, credentialsFile string, readOnlyMode bool, proxyURL string) (*BigQueryDB, error) {
	ctx := context.Background()
	var clientOptions []option.ClientOption

	// 读取凭证文件
	credData, err := os.ReadFile(credentialsFile)
	if err != nil {
		return nil, fmt.Errorf("failed to read credentials file: %w", err)
	}

	if proxyURL != "" {
		// 测试代理连接
		if err := testProxyConnection(proxyURL); err != nil {
			log.Printf("代理连接测试失败: %v", err)
			log.Printf("建议: 1) 检查代理服务器是否运行 2) 确认代理端口配置正确 3) 尝试使用HTTP代理而非SOCKS5")
			return nil, fmt.Errorf("proxy connection test failed: %w", err)
		}

		proxyURLParsed, err := url.Parse(proxyURL)
		if err != nil {
			return nil, fmt.Errorf("invalid proxy URL: %w", err)
		}

		var transport *http.Transport
		if strings.HasPrefix(proxyURL, "socks5://") {
			dialer, err := proxy.FromURL(proxyURLParsed, proxy.Direct)
			if err != nil {
				return nil, fmt.Errorf("failed to create SOCKS5 dialer: %w", err)
			}
			transport = &http.Transport{
				DialContext:           dialer.(proxy.ContextDialer).DialContext,
				MaxIdleConns:          100,
				IdleConnTimeout:       90 * time.Second,
				TLSHandshakeTimeout:   30 * time.Second,
				ExpectContinueTimeout: 10 * time.Second,
			}
			log.Printf("使用SOCKS5代理: %s", proxyURL)
		} else {
			transport = &http.Transport{
				Proxy:                 http.ProxyURL(proxyURLParsed),
				MaxIdleConns:          100,
				IdleConnTimeout:       90 * time.Second,
				TLSHandshakeTimeout:   30 * time.Second,
				ExpectContinueTimeout: 10 * time.Second,
			}
			log.Printf("使用HTTP/HTTPS代理: %s", proxyURL)
		}

		// 创建带代理的 HTTP 客户端
		httpClient := &http.Client{
			Transport: transport,
			Timeout:   120 * time.Second,
		}
		
		// 为 OAuth2 设置代理客户端
		ctx = context.WithValue(ctx, oauth2.HTTPClient, httpClient)
		
		// 解析 service account JSON
		config, err := google.JWTConfigFromJSON(credData, bigquery.Scope)
		if err != nil {
			return nil, fmt.Errorf("failed to parse JWT config: %w", err)
		}
		
		// 使用带代理的 context 创建 token source
		tokenSource := config.TokenSource(ctx)
		
		// 创建带代理和 OAuth2 的客户端
		oauth2Client := &http.Client{
			Transport: &oauth2.Transport{
				Source: tokenSource,
				Base:   transport,
			},
			Timeout: 120 * time.Second,
		}
		
		// 创建 credentials 用于 BigQuery SDK
		creds := &google.Credentials{
			TokenSource: tokenSource,
		}
		
		// 同时设置 credentials 和 HTTP 客户端
		clientOptions = append(clientOptions, option.WithCredentials(creds))
		clientOptions = append(clientOptions, option.WithHTTPClient(oauth2Client))
		log.Printf("BigQuery 使用代理: %s", proxyURL)
	} else {
		log.Printf("BigQuery 不使用代理 (直接连接)")
		// 不使用代理时，直接使用凭证文件
		clientOptions = append(clientOptions, option.WithCredentialsFile(credentialsFile))
	}

	authClient, err := bigquery.NewClient(ctx, authProjectID, clientOptions...)
	if err != nil {
		return nil, fmt.Errorf("could not create BigQuery auth client for project %s: %w", authProjectID, err)
	}

	var dataClient *bigquery.Client
	if dataProjectID != authProjectID {
		dataClient, err = bigquery.NewClient(ctx, dataProjectID, clientOptions...)
		if err != nil {
			authClient.Close()
			return nil, fmt.Errorf("could not create BigQuery data client for project %s: %w", dataProjectID, err)
		}
	} else {
		dataClient = authClient
	}

	return &BigQueryDB{
		authClient:    authClient,
		dataClient:    dataClient,
		authProjectID: authProjectID,
		dataProjectID: dataProjectID,
		dataset:       dataset,
		ctx:           ctx,
		readOnlyMode:  readOnlyMode,
		logDir:        globalLogDir,
		logPrefix:     "bigquery",
	}, nil
}

// SetLogPrefix sets per instance file prefix for logs
func (bq *BigQueryDB) SetLogPrefix(prefix string) {
	bq.logPrefix = prefix
}

// writeQueryLog writes the SQL and result to markdown log file in logDir.
func (bq *BigQueryDB) writeQueryLog(sqlContent string, queryResult interface{}) {
	if bq.logDir == "" {
		return
	}

	now := time.Now()
	dateDir := filepath.Join(bq.logDir, now.Format("2006-01-02"))
	if err := os.MkdirAll(dateDir, 0755); err != nil {
		return
	}

	prefix := bq.logPrefix
	if prefix == "" {
		prefix = "bigquery"
	}
	filePath := filepath.Join(dateDir, fmt.Sprintf("%s_%s.md", prefix, now.Format("15_04_05")))

	jsonBytes, err := json.MarshalIndent(queryResult, "", "  ")
	if err != nil {
		jsonBytes = []byte(fmt.Sprintf("%v", queryResult))
	}

	content := fmt.Sprintf("## SQL\n```sql\n%s\n```\n\n## Result\n```json\n%s\n```\n", sqlContent, string(jsonBytes))
	_ = os.WriteFile(filePath, []byte(content), 0644)
}

func (bq *BigQueryDB) Close() error {
	var errs []error

	if bq.authClient != nil {
		if err := bq.authClient.Close(); err != nil {
			errs = append(errs, fmt.Errorf("failed to close auth client: %w", err))
		}
	}

	// Only close data client if it's different from auth client
	if bq.dataClient != nil && bq.dataClient != bq.authClient {
		if err := bq.dataClient.Close(); err != nil {
			errs = append(errs, fmt.Errorf("failed to close data client: %w", err))
		}
	}

	if len(errs) > 0 {
		return fmt.Errorf("errors closing BigQuery clients: %v", errs)
	}
	return nil
}

func (bq *BigQueryDB) IsReadOnlyQuery(query string) bool {
	return IsReadOnlyQuery(query)
}

// splitSQLStatements 分割多条SQL语句 (与MySQL版本相同)
func (bq *BigQueryDB) splitSQLStatements(sqlContent string) []string {
	// 简单的SQL语句分割逻辑
	// 按分号分割，但要考虑字符串中的分号
	var statements []string
	var current strings.Builder
	inQuotes := false
	inDoubleQuotes := false
	escaped := false

	for _, char := range sqlContent {
		switch char {
		case '\\':
			escaped = !escaped
			current.WriteRune(char)
		case '\'':
			if !escaped && !inDoubleQuotes {
				inQuotes = !inQuotes
			}
			escaped = false
			current.WriteRune(char)
		case '"':
			if !escaped && !inQuotes {
				inDoubleQuotes = !inDoubleQuotes
			}
			escaped = false
			current.WriteRune(char)
		case ';':
			escaped = false
			if !inQuotes && !inDoubleQuotes {
				// 分号在字符串外，这是一个语句结束
				stmt := strings.TrimSpace(current.String())
				if stmt != "" {
					statements = append(statements, stmt)
				}
				current.Reset()
			} else {
				current.WriteRune(char)
			}
		default:
			escaped = false
			current.WriteRune(char)
		}
	}

	// 处理最后一个语句（可能没有分号结尾）
	stmt := strings.TrimSpace(current.String())
	if stmt != "" {
		statements = append(statements, stmt)
	}

	return statements
}

// executeSingleQuery 执行单条SQL语句
func (bq *BigQueryDB) executeSingleQuery(query string) (interface{}, error) {
	// Use auth client for executing queries (billing goes to auth project)
	q := bq.authClient.Query(query)

	it, err := q.Read(bq.ctx)
	if err != nil {
		// 如果是权限错误，记录并返回更友好的错误
		if strings.Contains(err.Error(), "Access Denied") || strings.Contains(err.Error(), "403") {
			log.Printf("BigQuery access denied for query: %s", query)
			return nil, fmt.Errorf("access denied to BigQuery resource")
		}
		return nil, fmt.Errorf("query execution failed: %w", err)
	}

	var results []map[string]interface{}
	for {
		var row map[string]bigquery.Value
		err := it.Next(&row)
		if err != nil {
			break

			// if err.Error() == "iterator done" {
			// 	break
			// }
			// return nil, fmt.Errorf("failed to read row: %w", err)
		}

		// Convert bigquery.Value to standard interface{}
		result := make(map[string]interface{})
		for k, v := range row {
			result[k] = v
		}
		results = append(results, result)
	}

	return results, nil
}

// ExecuteMultipleQueries 执行多条SQL语句（外部接口，会进行表名补全）
func (bq *BigQueryDB) ExecuteMultipleQueries(sqlContent string) ([]map[string]interface{}, error) {
	// 先补全表名
	sqlContent = bq.autoCompleteTableNames(sqlContent)
	statements := bq.splitSQLStatements(sqlContent)
	return bq.executeMultipleStatementsInternal(statements)
}

// executeMultipleStatementsInternal 内部执行多条语句（不再补全表名）
func (bq *BigQueryDB) executeMultipleStatementsInternal(statements []string) ([]map[string]interface{}, error) {
	if len(statements) == 0 {
		return nil, fmt.Errorf("no valid SQL statements found")
	}

	var results []map[string]interface{}

	for i, stmt := range statements {
		// 检查当前语句是否是只读查询
		isReadOnly := bq.IsReadOnlyQuery(stmt)

		// 如果在只读模式下且不是只读查询，则跳过该语句
		if bq.readOnlyMode && !isReadOnly {
			errorResult := map[string]interface{}{
				"statement_index": i + 1,
				"statement":       stmt,
				"error":           "write operations are not allowed in read-only mode",
			}
			results = append(results, errorResult)
			continue
		}

		// 执行单条语句（已经补全过表名了）
		result, err := bq.executeSingleQuery(stmt)
		if err != nil {
			// 返回错误信息，包括语句序号
			errorResult := map[string]interface{}{
				"statement_index": i + 1,
				"statement":       stmt,
				"error":           err.Error(),
			}
			results = append(results, errorResult)
			// 继续执行剩余的语句，而不是立即返回错误
		} else {
			// 成功执行的结果
			successResult := map[string]interface{}{
				"statement_index": i + 1,
				"statement":       stmt,
				"result":          result,
			}
			results = append(results, successResult)
		}
	}

	return results, nil
}

// autoCompleteTableNames 自动补全表名，将 dataset.table 转换为 project.dataset.table
func (bq *BigQueryDB) autoCompleteTableNames(query string) string {
	// 匹配表名：带或不带反引号的 xxx.yyy 或 xxx.yyy.zzz 格式
	// 支持字母、数字、下划线、连字符
	re := regexp.MustCompile("`?([a-zA-Z0-9_-]+(?:\\.[a-zA-Z0-9_-]+)+)`?")
	
	result := re.ReplaceAllStringFunc(query, func(match string) string {
		// 移除反引号以便分析
		tableName := strings.Trim(match, "`")
		
		// 统计点的数量
		dotCount := strings.Count(tableName, ".")
		
		// 如果只有1个点（两段式：dataset.table），需要补全
		if dotCount == 1 {
			// 补全为三段式
			return fmt.Sprintf("`%s.%s`", bq.dataProjectID, tableName)
		}
		
		// 如果是2个点（三段式：project.dataset.table）或更多，保持原样
		// 如果原来有反引号就保留，没有就添加
		if strings.HasPrefix(match, "`") {
			return match
		} else {
			// 为三段式表名添加反引号
			return fmt.Sprintf("`%s`", tableName)
		}
	})
	
	return result
}

func (bq *BigQueryDB) ExecuteQuery(query string) (interface{}, error) {
	// 自动补全表名（只补全一次）
	query = bq.autoCompleteTableNames(query)
	
	// 检查是否包含多条语句
	statements := bq.splitSQLStatements(query)
	if len(statements) > 1 {
		// 如果有多条语句，直接执行多条语句（已经补全过了）
		return bq.executeMultipleStatementsInternal(statements)
	}

	// 单条语句的处理逻辑
	return bq.executeSingleQuery(query)
}

func (bq *BigQueryDB) GetTables(includePatterns, excludePatterns []string) ([]string, error) {
	// Use data client for listing tables (metadata operations)
	it := bq.dataClient.Dataset(bq.dataset).Tables(bq.ctx)
	var allTables []string
	for {
		table, err := it.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			// 如果是权限错误，返回空列表而不是错误
			if strings.Contains(err.Error(), "Access Denied") || strings.Contains(err.Error(), "403") {
				log.Printf("BigQuery access denied for dataset %s.%s, skipping table enumeration", bq.dataProjectID, bq.dataset)
				return []string{}, nil
			}
			return nil, fmt.Errorf("failed to list tables: %w", err)
		}
		allTables = append(allTables, table.TableID)
	}

	// Apply include and exclude patterns (existing logic)
	var includedTables []string
	if len(includePatterns) > 0 {
		for _, pattern := range includePatterns {
			re, err := regexp.Compile(pattern)
			if err != nil {
				return nil, fmt.Errorf("invalid include regex pattern '%s': %w", pattern, err)
			}
			for _, table := range allTables {
				if re.MatchString(table) {
					includedTables = append(includedTables, table)
				}
			}
		}
	} else {
		includedTables = allTables
	}

	var finalTables []string
	if len(excludePatterns) > 0 {
		for _, pattern := range excludePatterns {
			re, err := regexp.Compile(pattern)
			if err != nil {
				return nil, fmt.Errorf("invalid exclude regex pattern '%s': %w", pattern, err)
			}
			var tempTables []string
			for _, table := range includedTables {
				if !re.MatchString(table) {
					tempTables = append(tempTables, table)
				}
			}
			includedTables = tempTables
		}
		finalTables = includedTables
	} else {
		finalTables = includedTables
	}

	return finalTables, nil
}

func (bq *BigQueryDB) GetTableDDL(tableName string) (string, error) {
	// Use data client for metadata operations
	table := bq.dataClient.Dataset(bq.dataset).Table(tableName)
	meta, err := table.Metadata(bq.ctx)
	if err != nil {
		if strings.Contains(err.Error(), "access denied") || strings.Contains(err.Error(), "Access Denied") {
			log.Printf("BigQuery access denied for table metadata: %s", tableName)
			return fmt.Sprintf("-- Metadata for table %s is not accessible due to permissions\n", tableName), nil
		}
		return "", fmt.Errorf("failed to get table metadata: %w", err)
	}

	var schemaInfo strings.Builder
	schemaInfo.WriteString(fmt.Sprintf("-- Schema for table: %s.%s.%s\n", bq.dataProjectID, bq.dataset, tableName))
	schemaInfo.WriteString("--\n")

	for _, field := range meta.Schema {
		schemaInfo.WriteString(fmt.Sprintf("COLUMN_NAME: %-40s DATA_TYPE: %s\n", field.Name, field.Type))
	}

	return schemaInfo.String(), nil
}

// formatBigQuerySQL 格式化 BigQuery SQL 语句（支持正确的层级缩进）
func (bq *BigQueryDB) formatBigQuerySQL(sql string) string {
	// 第一步：标准化和关键字大写
	sql = bq.normalizeAndUppercase(sql)
	
	// 第二步：智能格式化带缩进
	return bq.smartFormat(sql)
}

// normalizeAndUppercase 标准化SQL并转换关键字为大写
func (bq *BigQueryDB) normalizeAndUppercase(sql string) string {
	// 基本清理
	sql = strings.TrimSpace(sql)
	
	// 标准化空白（保留字符串内容）
	var result strings.Builder
	inString := false
	stringChar := byte(0)
	inBacktick := false
	prevSpace := false
	
	for i := 0; i < len(sql); i++ {
		ch := sql[i]
		
		// 处理反引号
		if ch == '`' && !inString {
			inBacktick = !inBacktick
			result.WriteByte(ch)
			prevSpace = false
			continue
		}
		
		// 处理字符串
		if !inBacktick && !inString && (ch == '\'' || ch == '"') {
			inString = true
			stringChar = ch
			result.WriteByte(ch)
			prevSpace = false
		} else if inString && ch == stringChar {
			result.WriteByte(ch)
			// 检查是否是转义
			if i+1 < len(sql) && sql[i+1] == stringChar {
				i++
				result.WriteByte(sql[i])
			} else {
				inString = false
			}
			prevSpace = false
		} else if inString || inBacktick {
			// 在字符串或反引号内，保持原样
			result.WriteByte(ch)
			prevSpace = false
		} else if ch == ' ' || ch == '\t' || ch == '\n' || ch == '\r' {
			// 多个空白字符合并为一个空格
			if !prevSpace && result.Len() > 0 {
				result.WriteByte(' ')
				prevSpace = true
			}
		} else {
			result.WriteByte(ch)
			prevSpace = false
		}
	}
	
	sql = result.String()
	
	// 关键字大写
	keywords := []string{
		// 主要子句
		"WITH", "SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", 
		"HAVING", "LIMIT", "OFFSET", "UNION", "UNION ALL", "EXCEPT", "INTERSECT",
		// JOIN
		"JOIN", "LEFT JOIN", "RIGHT JOIN", "INNER JOIN", "OUTER JOIN", "FULL JOIN", "CROSS JOIN", "ON",
		// 逻辑
		"AND", "OR", "NOT", "IN", "EXISTS", "BETWEEN", "LIKE", "IS", "NULL", "AS",
		"DISTINCT", "ALL", "ANY", "SOME",
		// CASE
		"CASE", "WHEN", "THEN", "ELSE", "END",
		// 窗口
		"PARTITION BY", "OVER", "WINDOW", "ROWS", "RANGE", "UNBOUNDED", "PRECEDING", "FOLLOWING", "CURRENT ROW",
		// 排序
		"ASC", "DESC", "NULLS FIRST", "NULLS LAST",
		// 数据类型
		"ARRAY", "STRUCT", "DATE", "DATETIME", "TIMESTAMP", "TIME", "STRING", "INT64", "FLOAT64", "BOOL", "BYTES",
	}
	
	// 分成单词和多词关键字
	for _, kw := range keywords {
		pattern := `(?i)\b` + strings.ReplaceAll(regexp.QuoteMeta(kw), ` `, `\s+`) + `\b`
		re := regexp.MustCompile(pattern)
		sql = re.ReplaceAllString(sql, strings.ToUpper(kw))
	}
	
	// 函数大写
	functions := []string{
		"COUNT", "SUM", "AVG", "MIN", "MAX", "STDDEV", "VARIANCE",
		"ROW_NUMBER", "RANK", "DENSE_RANK", "NTILE", "PERCENT_RANK", "CUME_DIST",
		"LAG", "LEAD", "FIRST_VALUE", "LAST_VALUE", "NTH_VALUE",
		"STRING_AGG", "ARRAY_AGG", "ANY_VALUE",
		"CAST", "SAFE_CAST", "EXTRACT", "FORMAT",
		"REGEXP_EXTRACT", "REGEXP_REPLACE", "REGEXP_CONTAINS",
		"JSON_EXTRACT", "JSON_VALUE", "JSON_QUERY",
		"COALESCE", "IFNULL", "NULLIF", "IF",
		"UPPER", "LOWER", "TRIM", "LTRIM", "RTRIM",
		"CONCAT", "SUBSTR", "SUBSTRING", "LENGTH", "REPLACE",
		"ROUND", "FLOOR", "CEIL", "ABS", "SIGN", "POWER", "SQRT", "EXP", "LN", "LOG", "LOG10",
	}
	
	for _, fn := range functions {
		re := regexp.MustCompile(`(?i)\b` + fn + `\s*\(`)
		sql = re.ReplaceAllStringFunc(sql, func(match string) string {
			return fn + "("
		})
	}
	
	return sql
}

// smartFormat 智能格式化SQL，处理缩进
func (bq *BigQueryDB) smartFormat(sql string) string {
	var result strings.Builder
	var tokens []string
	
	// 改进的分词器 - 保留更多结构信息
	tokens = bq.tokenizeForFormat(sql)
	
	// 格式化处理状态
	depth := 0           // 括号深度
	selectDepth := -1    // SELECT字段列表的深度
	inSelectList := false // 是否在SELECT字段列表中
	inWhereClause := false // 是否在WHERE子句中
	lastWasKeyword := false // 上一个是否是关键字
	
	for i := 0; i < len(tokens); i++ {
		token := tokens[i]
		upperToken := strings.ToUpper(token)
		
		prevToken := ""
		if i > 0 {
			prevToken = strings.ToUpper(tokens[i-1])
		}
		
		nextToken := ""
		if i+1 < len(tokens) {
			nextToken = strings.ToUpper(tokens[i+1])
		}
		
		// 处理括号
		if token == "(" {
			result.WriteString(token)
			depth++
			// 检查是否是子查询开始
			if nextToken == "SELECT" || nextToken == "WITH" {
				result.WriteString("\n")
			}
			continue
		} else if token == ")" {
			depth--
			// 如果是子查询结束，可能需要缩进
			if depth < selectDepth {
				selectDepth = -1
				inSelectList = false
			}
			result.WriteString(token)
			continue
		}
		
		// 处理逗号
		if token == "," {
			result.WriteString(token)
			// 在SELECT列表中，逗号后换行
			if inSelectList {
				result.WriteString("\n" + strings.Repeat("\t", depth+1))
			} else {
				result.WriteString(" ")
			}
			continue
		}
		
		// 主要关键字处理
		switch upperToken {
		case "WITH":
			// WITH总是在新行开始
			if result.Len() > 0 && !strings.HasSuffix(result.String(), "\n") {
				result.WriteString("\n")
			}
			result.WriteString(strings.Repeat("\t", depth) + upperToken + " ")
			lastWasKeyword = true
			
		case "AS":
			result.WriteString(" " + upperToken + " ")
			lastWasKeyword = true
			
		case "SELECT":
			// SELECT前换行（除非在括号后）
			if !strings.HasSuffix(result.String(), "(") && !strings.HasSuffix(result.String(), "\n") {
				result.WriteString("\n")
			}
			result.WriteString(strings.Repeat("\t", depth) + upperToken + "\n")
			result.WriteString(strings.Repeat("\t", depth+1))
			selectDepth = depth
			inSelectList = true
			inWhereClause = false
			lastWasKeyword = true
			
		case "FROM":
			result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken + "\n")
			result.WriteString(strings.Repeat("\t", depth+1))
			inSelectList = false
			inWhereClause = false
			lastWasKeyword = true
			
		case "WHERE":
			result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken + "\n")
			result.WriteString(strings.Repeat("\t", depth+1))
			inSelectList = false
			inWhereClause = true
			lastWasKeyword = true
			
		case "GROUP":
			if nextToken == "BY" {
				result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken)
				inSelectList = false
				inWhereClause = false
				lastWasKeyword = true
			} else {
				result.WriteString(upperToken + " ")
			}
			
		case "ORDER":
			if nextToken == "BY" {
				result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken)
				inSelectList = false
				inWhereClause = false
				lastWasKeyword = true
			} else {
				result.WriteString(upperToken + " ")
			}
			
		case "BY":
			result.WriteString(" " + upperToken + "\n")
			result.WriteString(strings.Repeat("\t", depth+1))
			lastWasKeyword = true
			
		case "HAVING":
			result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken + "\n")
			result.WriteString(strings.Repeat("\t", depth+1))
			inSelectList = false
			inWhereClause = false
			lastWasKeyword = true
			
		case "LIMIT", "OFFSET":
			result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken + " ")
			inSelectList = false
			inWhereClause = false
			lastWasKeyword = true
			
		case "UNION", "EXCEPT", "INTERSECT":
			result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken)
			if nextToken == "ALL" || nextToken == "DISTINCT" {
				result.WriteString(" ")
			} else {
				result.WriteString("\n")
			}
			inSelectList = false
			inWhereClause = false
			lastWasKeyword = true
			
		case "ALL", "DISTINCT":
			if prevToken == "UNION" || prevToken == "SELECT" {
				result.WriteString(upperToken)
				if prevToken == "UNION" {
					result.WriteString("\n")
				} else {
					result.WriteString(" ")
				}
			} else {
				result.WriteString(upperToken + " ")
			}
			lastWasKeyword = true
			
		case "AND", "OR":
			if inWhereClause {
				result.WriteString("\n" + strings.Repeat("\t", depth+1) + upperToken + " ")
			} else {
				result.WriteString(" " + upperToken + " ")
			}
			lastWasKeyword = true
			
		case "CASE":
			result.WriteString(upperToken + "\n")
			result.WriteString(strings.Repeat("\t", depth+2))
			lastWasKeyword = true
			
		case "WHEN":
			if prevToken != "," {
				result.WriteString("\n" + strings.Repeat("\t", depth+2))
			}
			result.WriteString(upperToken + " ")
			lastWasKeyword = true
			
		case "THEN":
			result.WriteString(" " + upperToken + " ")
			lastWasKeyword = true
			
		case "ELSE":
			result.WriteString("\n" + strings.Repeat("\t", depth+2) + upperToken + " ")
			lastWasKeyword = true
			
		case "END":
			if prevToken == "THEN" || prevToken == "ELSE" || strings.Contains(result.String(), "CASE") {
				result.WriteString("\n" + strings.Repeat("\t", depth+1) + upperToken)
			} else {
				result.WriteString(upperToken)
			}
			lastWasKeyword = true
			
		case "JOIN", "LEFT", "RIGHT", "INNER", "OUTER", "FULL", "CROSS":
			if strings.Contains(nextToken, "JOIN") || nextToken == "JOIN" {
				result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken)
				if nextToken != "JOIN" {
					result.WriteString(" ")
				}
			} else if upperToken == "JOIN" {
				result.WriteString("\n" + strings.Repeat("\t", depth) + upperToken + " ")
			} else {
				result.WriteString(upperToken + " ")
			}
			lastWasKeyword = true
			
		case "ON":
			result.WriteString(" " + upperToken + " ")
			lastWasKeyword = true
			
		case "IN":
			result.WriteString(" " + upperToken + " ")
			lastWasKeyword = true
			
		case "OVER":
			result.WriteString(" " + upperToken + " ")
			lastWasKeyword = true
			
		case "PARTITION":
			if nextToken == "BY" {
				result.WriteString(upperToken)
			} else {
				result.WriteString(upperToken + " ")
			}
			lastWasKeyword = true
			
		default:
			// 处理函数和普通标识符
			if lastWasKeyword {
				// 关键字后的内容不需要额外缩进
				lastWasKeyword = false
			}
			
			// 检查是否是函数调用
			if nextToken == "(" {
				result.WriteString(token)
			} else {
				result.WriteString(token)
				// 添加空格（除非下一个是特殊字符）
				if i+1 < len(tokens) && nextToken != "," && nextToken != ")" && nextToken != "(" {
					result.WriteString(" ")
				}
			}
		}
	}
	
	// 清理格式
	formatted := result.String()
	// 清理多余的空白行
	formatted = regexp.MustCompile(`\n\s*\n\s*\n`).ReplaceAllString(formatted, "\n\n")
	// 清理行尾空格
	formatted = regexp.MustCompile(` +\n`).ReplaceAllString(formatted, "\n")
	// 清理多余空格
	formatted = regexp.MustCompile(` {2,}`).ReplaceAllString(formatted, " ")
	formatted = strings.TrimSpace(formatted)
	
	return formatted
}

// tokenizeForFormat 专门为格式化设计的分词器
func (bq *BigQueryDB) tokenizeForFormat(sql string) []string {
	var tokens []string
	var current strings.Builder
	inString := false
	stringChar := byte(0)
	inBacktick := false
	
	for i := 0; i < len(sql); i++ {
		ch := sql[i]
		
		// 处理反引号
		if ch == '`' && !inString {
			if inBacktick {
				current.WriteByte(ch)
				tokens = append(tokens, current.String())
				current.Reset()
				inBacktick = false
			} else {
				if current.Len() > 0 {
					tokens = append(tokens, current.String())
					current.Reset()
				}
				current.WriteByte(ch)
				inBacktick = true
			}
			continue
		}
		
		if inBacktick {
			current.WriteByte(ch)
			continue
		}
		
		// 处理字符串
		if !inString && (ch == '\'' || ch == '"') {
			if current.Len() > 0 {
				tokens = append(tokens, current.String())
				current.Reset()
			}
			inString = true
			stringChar = ch
			current.WriteByte(ch)
		} else if inString {
			current.WriteByte(ch)
			if ch == stringChar {
				if i+1 < len(sql) && sql[i+1] == stringChar {
					i++
					current.WriteByte(sql[i])
				} else {
					tokens = append(tokens, current.String())
					current.Reset()
					inString = false
				}
			}
		} else if ch == ' ' || ch == '\t' || ch == '\n' || ch == '\r' {
			// 空白字符作为分隔符
			if current.Len() > 0 {
				tokens = append(tokens, current.String())
				current.Reset()
			}
		} else if ch == '(' || ch == ')' || ch == ',' || ch == ';' {
			// 括号、逗号、分号单独作为token
			if current.Len() > 0 {
				tokens = append(tokens, current.String())
				current.Reset()
			}
			tokens = append(tokens, string(ch))
		} else if ch == '=' || ch == '<' || ch == '>' || ch == '!' {
			// 比较操作符（但不包括减号，因为减号可能是表名的一部分）
			if current.Len() > 0 {
				tokens = append(tokens, current.String())
				current.Reset()
			}
			
			// 处理多字符操作符
			if (ch == '<' || ch == '>' || ch == '!') && i+1 < len(sql) && sql[i+1] == '=' {
				tokens = append(tokens, string(ch)+string(sql[i+1]))
				i++
			} else if ch == '<' && i+1 < len(sql) && sql[i+1] == '>' {
				tokens = append(tokens, "<>")
				i++
			} else {
				tokens = append(tokens, string(ch))
			}
		} else if ch == '+' || ch == '*' || ch == '/' {
			// 算术操作符（不包括减号）
			if current.Len() > 0 {
				tokens = append(tokens, current.String())
				current.Reset()
			}
			tokens = append(tokens, string(ch))
		} else if ch == '-' {
			// 减号需要特殊处理：
			// 1. 如果在标识符中间（如 project-id），保留
			// 2. 如果前后有空格，可能是操作符
			// 简化处理：只在明确的空格前后才作为操作符
			if current.Len() > 0 {
				// 检查下一个字符
				if i+1 < len(sql) {
					nextCh := sql[i+1]
					// 如果下一个是空格或特殊字符，则作为操作符
					if nextCh == ' ' || nextCh == '\t' || nextCh == '\n' || nextCh == '\r' || 
					   nextCh == '(' || nextCh == ')' || nextCh == ',' {
						tokens = append(tokens, current.String())
						current.Reset()
						tokens = append(tokens, string(ch))
					} else {
						// 否则作为标识符的一部分（如 project-id）
						current.WriteByte(ch)
					}
				} else {
					// 如果是最后一个字符
					tokens = append(tokens, current.String())
					current.Reset()
					tokens = append(tokens, string(ch))
				}
			} else {
				// current为空，可能是负号或单独的减号
				current.WriteByte(ch)
			}
		} else {
			current.WriteByte(ch)
		}
	}
	
	if current.Len() > 0 {
		tokens = append(tokens, current.String())
	}
	
	return tokens
}

// addNewlineBeforeClause 在主层级子句前添加换行
func addNewlineBeforeClause(sql string, clause string) string {
	// 简单方法：查找子句，检查前面的括号平衡
	result := ""
	index := 0
	
	re := regexp.MustCompile(`\b` + regexp.QuoteMeta(clause) + `\b`)
	matches := re.FindAllStringIndex(sql, -1)
	
	for _, match := range matches {
		// 检查括号平衡
		before := sql[index:match[0]]
		openCount := strings.Count(before, "(")
		closeCount := strings.Count(before, ")")
		
		if openCount == closeCount {
			// 在主层级，添加换行
			result += before
			if len(result) > 0 && result[len(result)-1] != '\n' {
				result += "\n"
			}
			result += sql[match[0]:match[1]]
			index = match[1]
		}
	}
	
	if index < len(sql) {
		result += sql[index:]
	}
	
	return result
}

// startsWithMainClause 检查行是否以主要子句开始
func startsWithMainClause(line string) bool {
	mainClauses := []string{
		"SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "HAVING", 
		"LIMIT", "OFFSET", "WITH", "UNION", "EXCEPT", "INTERSECT",
	}
	
	for _, clause := range mainClauses {
		if strings.HasPrefix(line, clause) {
			return true
		}
	}
	return false
}

// normalizeWhitespace 标准化空白字符，保留字符串内容
func normalizeWhitespace(sql string) string {
	var result strings.Builder
	inString := false
	stringChar := byte(0)
	inBacktick := false
	prevSpace := false
	
	for i := 0; i < len(sql); i++ {
		ch := sql[i]
		
		// 处理反引号
		if ch == '`' && !inString {
			inBacktick = !inBacktick
			result.WriteByte(ch)
			prevSpace = false
			continue
		}
		
		// 处理字符串
		if !inBacktick && !inString && (ch == '\'' || ch == '"') {
			inString = true
			stringChar = ch
			result.WriteByte(ch)
			prevSpace = false
		} else if inString && ch == stringChar {
			result.WriteByte(ch)
			// 检查是否是转义
			if i+1 < len(sql) && sql[i+1] == stringChar {
				i++
				result.WriteByte(sql[i])
			} else {
				inString = false
			}
			prevSpace = false
		} else if inString || inBacktick {
			// 在字符串或反引号内，保持原样
			result.WriteByte(ch)
			prevSpace = false
		} else if ch == ' ' || ch == '\t' || ch == '\n' || ch == '\r' {
			// 多个空白字符合并为一个空格
			if !prevSpace {
				result.WriteByte(' ')
				prevSpace = true
			}
		} else {
			result.WriteByte(ch)
			prevSpace = false
		}
	}
	
	return result.String()
}

// formatMainClause 格式化主要子句
func formatMainClause(sql string, clause string) string {
	// 只在非括号内的子句前加换行
	pattern := fmt.Sprintf(`(\s+)(%s)(\s+)`, regexp.QuoteMeta(clause))
	re := regexp.MustCompile(pattern)
	
	return re.ReplaceAllStringFunc(sql, func(match string) string {
		// 简单检查是否在括号内（这是一个简化的检查）
		beforeMatch := sql[:strings.Index(sql, match)]
		openParens := strings.Count(beforeMatch, "(")
		closeParens := strings.Count(beforeMatch, ")")
		
		if openParens > closeParens {
			// 在括号内，保持原样
			return match
		}
		// 主要子句，添加换行
		return "\n" + clause + "\n\t"
	})
}

// formatSelectList 格式化 SELECT 列表中的逗号
func formatSelectList(sql string) string {
	lines := strings.Split(sql, "\n")
	var result []string
	inSelect := false
	
	for _, line := range lines {
		trimmedLine := strings.TrimSpace(line)
		
		if strings.HasPrefix(trimmedLine, "SELECT") {
			inSelect = true
			result = append(result, line)
		} else if inSelect && strings.HasPrefix(trimmedLine, "FROM") {
			inSelect = false
			result = append(result, line)
		} else if inSelect {
			// 在 SELECT 子句中，格式化逗号
			if strings.Contains(line, ",") {
				// 简单处理：每个逗号后换行
				parts := strings.Split(line, ",")
				for i, part := range parts {
					if i == 0 {
						result = append(result, part+",")
					} else if i < len(parts)-1 {
						result = append(result, "\t"+strings.TrimSpace(part)+",")
					} else {
						if strings.TrimSpace(part) != "" {
							result = append(result, "\t"+strings.TrimSpace(part))
						}
					}
				}
			} else {
				result = append(result, line)
			}
		} else {
			result = append(result, line)
		}
	}
	
	return strings.Join(result, "\n")
}

func (bq *BigQueryDB) ProcessSQLFile(sqlPath string) error {
	content, err := os.ReadFile(sqlPath)
	if err != nil {
		return fmt.Errorf("could not read SQL file: %w", err)
	}

	// 读取文件内容并去除首尾空白
	originalSQL := string(content)
	sqlContent := strings.TrimSpace(originalSQL)
	if sqlContent == "" {
		// 空文件，直接返回
		return nil
	}

	// 检查最后一行是否是 GO
	lines := strings.Split(sqlContent, "\n")
	lastLine := strings.TrimSpace(lines[len(lines)-1])

	// 如果是非只读查询且最后一行不是 GO，则跳过
	isReadOnly := bq.IsReadOnlyQuery(sqlContent)
	if !isReadOnly && lastLine != "GO" {
		return nil
	}

	// 如果是 GO 命令，则删除该行
	if lastLine == "GO" {
		sqlContent = strings.Join(lines[:len(lines)-1], "\n")
	}

	// 执行查询
	var queryResult interface{}
	queryResult, err = bq.ExecuteQuery(sqlContent)
	if err != nil {
		queryResult = map[string]string{"error": err.Error()}
	}

	// 标准化查询结果中的时间格式
	queryResult = bq.normalizeTimeInResult(queryResult)

	// 写入 JSON 文件
	jsonData, err := json.MarshalIndent(queryResult, "", "  ")
	if err != nil {
		return fmt.Errorf("could not marshal results: %w", err)
	}

	basePath := strings.TrimSuffix(sqlPath, filepath.Ext(sqlPath))
	mdPath := basePath + ".md"

	// 格式化 SQL（用于显示在 markdown 中）
	formattedSQL := bq.formatBigQuerySQL(sqlContent)

	mdContent := fmt.Sprintf("## SQL\n```sql\n%s\n```\n\n## Result\n```json\n%s\n```\n", formattedSQL, string(jsonData))

	if err := os.WriteFile(mdPath, []byte(mdContent), 0644); err != nil {
		return fmt.Errorf("could not write result markdown file: %w", err)
	}

	// 格式化后写回 SQL 文件（只在内容真正改变时写入）
	formattedSQLWithNewline := formattedSQL + "\n"
	if formattedSQLWithNewline != originalSQL {
		// 标记文件正在格式化，避免循环触发
		SetFormattingFile(sqlPath, true)
		defer func() {
			// 延迟 500ms 后重置状态，给文件监听器足够时间处理
			time.Sleep(500 * time.Millisecond)
			SetFormattingFile(sqlPath, false)
		}()
		
		if err := os.WriteFile(sqlPath, []byte(formattedSQLWithNewline), 0644); err != nil {
			log.Printf("警告: 无法写入格式化的 SQL: %v", err)
		} else {
			log.Printf("已格式化 SQL 文件: %s", sqlPath)
		}
	}

	bq.writeQueryLog(formattedSQL, queryResult)

	return nil
}

func (bq *BigQueryDB) InitializeTableFiles(workDir string, includeTablePatterns, excludeTablePatterns []string) error {
	tables, err := bq.GetTables(includeTablePatterns, excludeTablePatterns)
	if err != nil {
		return err
	}

	for _, table := range tables {
		// 修改这里，为每个 dataset 创建子目录
		datasetDir := filepath.Join(workDir, "tables", bq.dataset)
		tableDir := filepath.Join(datasetDir, table)
		if err := os.MkdirAll(tableDir, 0755); err != nil {
			return fmt.Errorf("failed to create directory for table %s: %w", table, err)
		}

		// 创建 query.sql
		querySQLPath := filepath.Join(tableDir, "query.sql")
		// Creating query.sql now needs to reference the data project ID.
		queryContent := fmt.Sprintf("SELECT * FROM `%s.%s.%s` LIMIT 100", bq.dataProjectID, bq.dataset, table)
		if err := os.WriteFile(querySQLPath, []byte(queryContent), 0644); err != nil {
			log.Printf("warning: could not write query.sql for table %s: %v", table, err)
		}

		// 获取并写入 schema.txt
		// schemaPath := filepath.Join(tableDir, "schema.txt")
		// schemaContent, err := bq.GetTableDDL(table)
		// if err != nil {
		// 	log.Printf("warning: could not get schema for table %s: %v", table, err)
		// 	schemaContent = fmt.Sprintf("-- Failed to retrieve schema for %s: %v", table, err)
		// }
		// if err := os.WriteFile(schemaPath, []byte(schemaContent), 0644); err != nil {
		// 	log.Printf("warning: could not write schema.txt for table %s: %v", table, err)
		// }
	}

	return nil
}

func (bq *BigQueryDB) GenerateDDLChange(ddlPath string) error {
	// BigQuery DDL比较功能有限，因为BigQuery的DDL结构与MySQL不同
	log.Printf("BigQuery DDL change generation not fully supported for file: %s", ddlPath)

	// 读取DDL文件内容以验证其存在
	_, err := os.ReadFile(ddlPath)
	if err != nil {
		return fmt.Errorf("could not read DDL file: %w", err)
	}

	// 创建一个占位符的change文件，表明BigQuery不支持此功能
	changeFilePath := filepath.Join(filepath.Dir(ddlPath), "ddl_change.sql")
	changeContent := "-- BigQuery DDL change generation is not supported\n-- Please use BigQuery console or bq command line tool for schema changes\n"

	if err := os.WriteFile(changeFilePath, []byte(changeContent), 0644); err != nil {
		return fmt.Errorf("could not write DDL change file: %w", err)
	}

	return nil
}

// ProcessJSONModification BigQuery不支持JSON修改生成UPDATE语句
func (bq *BigQueryDB) ProcessJSONModification(jsonPath string) error {
	// BigQuery不支持此功能，因为BigQuery是只读分析数据库
	log.Printf("BigQuery does not support JSON modification for UPDATE generation: %s", jsonPath)
	return nil
}

// ProcessInsertSQL 处理insert.sql文件，将JSON内容转换为INSERT语句
func (bq *BigQueryDB) ProcessInsertSQL(insertPath string) error {
	// BigQuery暂时不支持此功能，按普通SQL文件处理
	return bq.ProcessSQLFile(insertPath)
}

// normalizeTimeInResult 标准化查询结果中的时间格式
func (bq *BigQueryDB) normalizeTimeInResult(result interface{}) interface{} {
	switch v := result.(type) {
	case []map[string]interface{}:
		// 处理查询结果数组
		for i, row := range v {
			for key, val := range row {
				v[i][key] = bq.normalizeTimeValue(val)
			}
		}
		return v
	case map[string]interface{}:
		// 处理单个结果对象
		for key, val := range v {
			v[key] = bq.normalizeTimeValue(val)
		}
		return v
	case []interface{}:
		// 处理接口数组
		for i, item := range v {
			v[i] = bq.normalizeTimeInResult(item)
		}
		return v
	default:
		return result
	}
}

// normalizeTimeValue 标准化单个值中的时间格式
func (bq *BigQueryDB) normalizeTimeValue(val interface{}) interface{} {
	switch v := val.(type) {
	case time.Time:
		// 转换time.Time为标准格式字符串
		return v.Format("2006-01-02 15:04:05")
	case string:
		// 处理时间字符串
		if bq.isTimeString(v) {
			return bq.normalizeTimeString(v)
		}
		return v
	default:
		return val
	}
}

// isTimeString 检查字符串是否是时间格式
func (bq *BigQueryDB) isTimeString(s string) bool {
	// 更精确的时间格式检测
	if len(s) < 10 {
		return false
	}

	// 检查是否包含日期格式 YYYY-MM-DD
	if len(s) >= 10 && s[4] == '-' && s[7] == '-' {
		// 检查年月日是否都是数字
		if bq.isDigit(s[0]) && bq.isDigit(s[1]) && bq.isDigit(s[2]) && bq.isDigit(s[3]) &&
			bq.isDigit(s[5]) && bq.isDigit(s[6]) && bq.isDigit(s[8]) && bq.isDigit(s[9]) {
			return true
		}
	}

	// 检查是否包含UTC时区信息
	if strings.Contains(s, " UTC") || strings.Contains(s, "+0000") {
		return true
	}

	return false
}

// isDigit 检查字符是否是数字
func (bq *BigQueryDB) isDigit(c byte) bool {
	return c >= '0' && c <= '9'
}

// normalizeTimeString 标准化时间字符串
func (bq *BigQueryDB) normalizeTimeString(s string) string {
	// 处理BigQuery返回的格式
	if strings.Contains(s, " UTC") {
		// 移除 " +0000 UTC" 或类似的时区信息
		if idx := strings.Index(s, " +"); idx != -1 {
			s = s[:idx]
		} else if idx := strings.Index(s, " UTC"); idx != -1 {
			s = s[:idx]
		}
	}

	// 移除末尾的Z
	if strings.HasSuffix(s, "Z") {
		s = s[:len(s)-1]
	}

	// 移除毫秒部分
	if idx := strings.Index(s, "."); idx != -1 {
		s = s[:idx]
	}

	// 统一T分隔符为空格
	s = strings.ReplaceAll(s, "T", " ")

	return s
}
